import python_baway.exercise.mysql.oop.MysqlHelper as MysqlHelper
from python_baway.common.xdb import xdb_args

xhelper = MysqlHelper.MysqlHelper(**xdb_args)

xtable_name = 'student_ppt14_page29'

xsql_drop = f'''\
DROP TABLE IF EXISTS {xtable_name}\
'''
xr = xhelper.exec(xsql_drop)
print(f'Affected rows: {xr}')

xsql_create = f'''\
CREATE TABLE IF NOT EXISTS {xtable_name}
(
    id int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL DEFAULT '',
    age tinyint(3) unsigned NOT NULL DEFAULT 0,
    height decimal(5,2) unsigned NOT NULL DEFAULT 0.0,
    gender enum('男', '女', '保密') NOT NULL DEFAULT '保密',
    certs set('H', 'Z', 'B', 'Bd', 'M', 'Md', 'D', 'Dd') NULL DEFAULT NULL
)
DEFAULT CHARACTER SET = utf8mb4
DEFAULT COLLATE = utf8mb4_general_ci\
'''
xr = xhelper.exec(xsql_create)
print(f'Affected rows: {xr}')

xsql = f"RENAME TABLE {xtable_name} TO {xtable_name}02"
xr = xhelper.exec(xsql)
print(f'Affected rows: {xr}')

xsql = f'''\
INSERT INTO {xtable_name}02
(name, age, height, gender, certs)
VALUES
('裴Pei 云鹏Yunpeng', 38, 1.81, '男', 'z,H,B,Bd'),
('杨Yang 旸Yang', 35, 1.82, 'M', 'H,B,Bd,M,Md'),
('王Wang 耀兵Yaobing', 62, 1.60, '女', 'D,Dd,H,B,Bd,M,Md')
'''
xr = xhelper.exec(xsql)
print(f'Affected rows: {xr}')

xsql = f"ALTER TABLE {xtable_name}02 RENAME TO {xtable_name}"
xr = xhelper.exec(xsql)
print(f'Affected rows: {xr}')

xhelper.close()
